﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using SiteManager;
//initial 1-30-2012
namespace SBS_webservicesDocMain
{
    public partial class SBS_fetchDentists : System.Web.UI.Page
    {
        Manager mgr = new Manager(true, "ConnectionString");
        private SqlDataAdapter m_DataAdapter = null;   
        //NOTE: dev table is named 'tbl_Dentist' but main/live has an s. No
        //wonder things take 2000 hours whent hey should take 5
        string remoteConnectDev = @"Data Source=74.54.54.98;User ID=SBSUSER;Password=DocDoc;Trusted_Connection=false;Initial Catalog=dbhaelthcare-dev";
       // string remoteConnect = @"Data Source=74.54.54.98;User ID=SBSUSER;Password=DocDoc;Trusted_Connection=false;Initial Catalog=dbhealthcare";
      string remoteConnect = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //string remoteConnect = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  
        private string lname = "";
        private string specialty = "";
        private string city = "";
        private string state = "";
        private string zipcodeforsearch = "";
        private string srating = "0.00";
        private double dRating = 0.00;
        private string tableName = "";
        //NEED PARAMS://this.m_UserName = base.Request.QueryString["UserName"].ToString();s
        public void ErrorMsg(string msg, XmlTextWriter writer)
        {
            writer.WriteStartElement("item");
            writer.WriteElementString("ERROR", msg);
            writer.WriteEndElement();
            writer.Flush();
            writer.Close();
            base.Response.End();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            XMLQuery();  
           // testPage();
            
        }
        private void testPage()
        {
            XmlTextWriter writer = new XmlTextWriter(base.Response.OutputStream, Encoding.UTF8);
            writer.WriteStartDocument();
            writer.WriteStartElement("rss");
            writer.WriteAttributeString("version", "2.0");
            writer.WriteStartElement("channel");

            writer.WriteElementString("title", "Find A Doc Web Services(Dentist) for smart devices or anything else");
            writer.WriteRaw("\n");
            writer.WriteElementString("link", "http://www.finadoc.com");
            writer.WriteRaw("\n");
            writer.WriteElementString("description", "Find A Doc WEB Service developed by Inayatullah)"); writer.WriteRaw("\n"); writer.WriteRaw("\n");
            
            
            //writer.WriteEndElement();
            //writer.WriteFullEndElement();

            //writer.WriteStartElement("rss");
            //writer.WriteAttributeString("version", "2.0");
            //writer.WriteStartElement("channel");


            writer.WriteRaw("\n"); writer.WriteRaw("\n");
            writer.WriteStartElement("item");
            writer.WriteElementString("item", "Items are to be added here"); writer.WriteRaw("\n"); writer.WriteRaw("\n");
            
            writer.WriteElementString("What", "What"); writer.WriteRaw("\n"); writer.WriteRaw("\n");
            
            
            writer.WriteEndElement();
            writer.Flush();
            writer.Close();
            base.Response.End();

        }
        //2-17-2012
        private void XMLQuery()
        {
            XmlTextWriter writer = new XmlTextWriter(base.Response.OutputStream, Encoding.UTF8);
            try
            {
                bool bErrors = false;
                writer.WriteStartDocument();
                writer.WriteStartElement("rss");
                writer.WriteAttributeString("version", "2.0");
                writer.WriteStartElement("channel");
                writer.WriteElementString("title", "Dental Find A Doc Web Services for smart devices or anything else");
                writer.WriteElementString("link", "http://www.finadoc.com");
                writer.WriteElementString("description", "Find A Doc WEB Service developed by www.southcoastsbs.com");

                if (base.Request.QueryString["LName"] != null)
                    this.lname = base.Request.QueryString["LName"].ToString();
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No Last Name was specified. LName=Donovan for example", writer);
                }
                //specialty
                if (base.Request.QueryString["Specialty"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.specialty = base.Request.QueryString["Specialty"].ToString();
                }
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No specialty given. Specialty=12", writer);
                }
                if (base.Request.QueryString["City"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.city = base.Request.QueryString["City"].ToString();
                }
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No City given. City=Boston", writer);
                }
                if (base.Request.QueryString["State"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.state = base.Request.QueryString["State"].ToString();
                }
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No State given. State=MA", writer);
                }
                //zip
                if (base.Request.QueryString["Zip"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.zipcodeforsearch = base.Request.QueryString["Zip"].ToString();
                }
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No zip code given. Zip=02127", writer);
                }
                //rating
                if (base.Request.QueryString["Rating"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.srating = base.Request.QueryString["Rating"].ToString();
                    try
                    {
                        dRating = Double.Parse(srating);
                    }
                    catch (Exception x)
                    {
                        //ignore
                        dRating = 0.00;
                    }
                }
                else
                {
                    //bErrors = true;
                    //ErrorMsg("No rating givne. Ex is Rating=70.00. This fetches 70 and greater.", writer);
                }

                if (bErrors == false)
                {
                    //  DONE validation 1-27-2012
                    //  BOSTON CHECK. NO ZEROS
                    
                    //  end boston zip cod fix
                    System.Collections.ArrayList values = new System.Collections.ArrayList();
                    string sql = " Select D.Id,D.FName,D.MName,D.LName,D.Address1,D.Address2,";
                    sql += "D.Phone,D.City,D.State,D.zipcodesearch,";
                    sql += "D.MedicalSchool,D.SpecialityID,";
                    sql += "D.InsuranceIds,D.AcceptNew,";
                    sql += "D.GroupPractice, D.PrimarySpecialityID,D.Languages,";
                    sql += "D.InsuranceIds,D.Cost, D.Abms_CertificationID,";
                    //2-5-2102 mods
                    //sql += " D.InsuranceIds, ";//D.Fellowship,D.HospitalName,";
                    //2-5-2012 mods end
                    sql += " sp.Specialty, ";//-->>> OK for doctor table sp2nd.Name, ";
                    sql += " md.medicalschool,";
                    sql += "aRating.DntOverAllRating, ";
                    //hosp aff
                    sql += "act.Name ";

                    //check since someone named the tables differently to bust chops
                    if (this.remoteConnect.Contains("Catalog=dbhealthcare1"))
                    {
                        sql += " From  tbl_Dentists D ";
                        this.tableName = "tbl_Dentists";
                    }
                    else
                    {
                        this.tableName = "tbl_Dentists";
                        sql += " From  tbl_Dentists D ";
                    }

                    sql += " left outer join tbl_DentistSpecialty sp on sp.id=D.specialityid";
                    //IGNORE 2nd or sub specialty for dentists. They are all blank
                    ///IGNORE ALERT DUE TO NO COLUMN VALUES
                    //---sql += " left outer join tbl_Secondaryspeciality sp2nd on sp2nd.id=D.PrimarySpecialityID";
                    sql += " left outer join tbl_MedicalSchool md on md.id = D.MedicalSchool ";
                    //sql += " left outer join tbl_MedicalSchool mr on mr.id = D.ResidencyTraining ";
                    sql += " left outer join tbl_dentOverAllRating aRating on aRating.Dnt_id=D.Id ";
                    //sql += " left outer join tbl_docOverAllRating aRating on aRating.Dr_id=D.Id ";
                    sql += " left outer join tbl_MajorActivity act on act.id=D.MajorActivityID ";

                    sql += " WHERE ( D.SpecialityID=" + specialty + ") ";
                    //lname check
                    if (!lname.Equals(""))
                        sql += " AND ( D.LName like '" + lname + "%' ) ";
                        
                    //sql += " AND( D.City like '{2}%' )";
                    ////--- 3-18-2012-mod sql += " AND( D.City like '{2}%' )";
                    if (!city.Equals(""))
                        sql += " AND( D.City like '" + city + "%' )";

                    //sql += " AND (D.State='{3}' ) ";
                    if (!state.Equals(""))
                        sql += " AND (D.State='" + state + "' ) ";
                    
                    if (this.city.ToLower().Trim().Equals("boston"))
                    {
                        //truncate for Boston because they are missing the zeroes
                        if (!zipcodeforsearch.Equals(""))
                            this.zipcodeforsearch = this.zipcodeforsearch.Substring(1);
                    }                        
                    
                    //---sql += " AND (D.zipcodesearch='{4}' ) ";
                    if (!zipcodeforsearch.Equals(""))
                        sql += " AND (D.zipcodesearch='" + zipcodeforsearch + "' ) ";
                    //rating
                    //----OLDCODE >>> sql += " AND( aRating.OverallRating >={5} )";
                    if (dRating != 0.00)
                        sql += " AND( aRating.DntOverallRating >=" + dRating + " ) ";
                    
                    sql += " Order by D.LName ";
                    
                    /*values.Add(specialty);
                    values.Add(lname);
                    values.Add(city);
                    values.Add(state);
                    values.Add(zipcodeforsearch);
                    values.Add(70.00);*/
                    //reset same var. yes
                    ///---NO SIRREE! We don't do thissql = String.Format(sql, values.ToArray());
                    
                    if (base.Request.QueryString["SQLDEBUG"] != null)
                    {
                        string[] sqs = sql.Split(',');
                        string sq = "";
                        for (int i = 0; i < sqs.Length; i++)
                        {
                            sq += sqs[i] + "," + "\r\n";
                        }
                        writer.WriteElementString("SQL", sq);
                    }
                    //debug end
                    
                    m_DataAdapter = new SqlDataAdapter(sql, remoteConnect);
                    
                    DataSet ds = new DataSet();
                    //---m_DataAdapter.Fill(ds, "tbl_doctors");
                    m_DataAdapter.Fill(ds, this.tableName);
                    DataTable dt = ds.Tables[0];
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        writer.WriteRaw("\n"); writer.WriteRaw("\n");
                        writer.WriteStartElement("item");
                        writer.WriteElementString("NORECORDS", "Zero Results");
                        writer.WriteEndElement();
                    }

                    //end header XML
                   
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow drow = dt.Rows[i];
                            writer.WriteStartElement("item");
                            writer.WriteRaw("\n"); writer.WriteRaw("\n");
                            string ID = drow["Id"].ToString();
                            writer.WriteElementString("Id", ID);
                            writer.WriteRaw("\n");
                            //full name                        
                            //string FName = drow["FName"].ToString();
                            //string MName = drow["MName"].ToString();
                            //string LName = drow["LName"].ToString();
                            //string fullName = FName + " " + MName.Trim() + " " + LName;
                            //writer.WriteElementString("Name", fullName);
                            //writer.WriteRaw("\n");
                            writer.WriteElementString("title", drow["FName"].ToString() + drow["MName"].ToString() + drow["LName"].ToString());
                            writer.WriteRaw("\n");
                            writer.WriteElementString("link", "http://www.finadoc.com");
                            writer.WriteRaw("\n");
                            //address 1
                            string Address1 = drow["Address1"].ToString();
                            writer.WriteElementString("Address1", Address1);
                            writer.WriteRaw("\n");
                            //city
                            string City = drow["City"].ToString();
                            writer.WriteElementString("City", City);
                            writer.WriteRaw("\n");
                            //state
                            string st = drow["State"].ToString();
                            writer.WriteElementString("state", st);
                            writer.WriteRaw("\n");
                            //zip. OPtional maybe
                            string zp = drow["zipcodesearch"].ToString();
                            writer.WriteElementString("Zipcode", zp);
                            writer.WriteRaw("\n");
                            //NOTE: first test produced NULL value, so watch this 2/16/2012
                            string specialtymain = drow["Specialty"].ToString();//note douche spelling wrong..                    
                            if (specialtymain.Trim().Equals(""))
                                specialtymain = "General Dentistry";
                            writer.WriteElementString("PrimarySpeciality", specialtymain);
                            writer.WriteRaw("\n");
                            string medschool1 = drow["medicalschool1"].ToString();
                            writer.WriteElementString("medicalschool", medschool1);
                            writer.WriteRaw("\n");
                            string acceptnew = drow["AcceptNew"].ToString();
                            if (acceptnew.Equals("") || acceptnew.Equals("1"))
                                acceptnew = "1";
                            else //either 1,0 or null
                                acceptnew = "0";
                            writer.WriteElementString("AcceptNew", acceptnew);
                            writer.WriteRaw("\n");
                            string group = drow["GroupPractice"].ToString();
                            writer.WriteElementString("GroupPractice", group);
                            writer.WriteRaw("\n");
                            /*string subsp = drow["PrimarySpecialityID"].ToString();//bad naming of fields..
                            writer.WriteElementString("PrimarySpecialityID", subsp);
                            writer.WriteRaw("\n");*/


                            //activity name
                            string activityName = drow["Name"].ToString();//bad naming of fields..
                            writer.WriteElementString("Activity", activityName);
                            writer.WriteRaw("\n");
                            //languages
                            string languages = drow["Languages"].ToString();//bad naming of fields..
                            writer.WriteElementString("Languages", languages);
                            writer.WriteRaw("\n");
                            //end activity
                            string OverAllRating = drow["DntOverAllRating"].ToString();
                            writer.WriteElementString("OverAllRating", OverAllRating);
                            writer.WriteRaw("\n");
                            //2-16-2012 fudge for now
                            string subRating = OverAllRating;
                            writer.WriteElementString("SubRating", subRating);
                            writer.WriteRaw("\n");
                            //insurance 2-16-2012
                            string ins = drow["InsuranceIds"].ToString();
                            if (ins.Equals(""))
                                ins = "0";
                            else
                                ins = "1";//meaning filled in of course = yes
                            writer.WriteElementString("Insurance", ins);
                            writer.WriteRaw("\n");
                            //2-16-2012 cost
                            string cost = drow["Cost"].ToString();
                            writer.WriteElementString("Cost", cost);
                            writer.WriteRaw("\n");
                            //board 2-16-2012
                            string board = drow["Abms_CertificationID"].ToString();
                            if (board.Equals("True"))
                                board = "1";
                            else
                                board = "0";
                            writer.WriteElementString("Board", board);
                            writer.WriteRaw("\n");
                            //---these are for compatibility so the UI won't gag
                            //fellowship
                            string fellow = "";//in requirments so it's here
                            writer.WriteElementString("Fellowship", fellow);
                            writer.WriteRaw("\n");
                            //residency too
                            string residency = "";
                            writer.WriteElementString("ResidencyTraining", residency);
                            writer.WriteRaw("\n");
                       
                        //cols
                        bool bDebug = false;
                        if (bDebug == true)
                        {
                            for (int x = 0; x < dt.Columns.Count; x++)
                            {
                                DataColumn dc = dt.Columns[x];
                                string name = dc.ColumnName;
                                writer.WriteElementString("ColumnName", name);
                                writer.WriteRaw("\n");
                            }
                        }
                        //insurance
                        /*string ins = drow["InsuranceIds"].ToString();//bad naming of fields..
                        writer.WriteElementString("Insurance", ins);
                        writer.WriteRaw("\n");*/
                        //insurance end
                        //he's a jolly good fellow
                        /*string fellow = drow["Fellowship"].ToString();//bad naming of fields..
                        writer.WriteElementString("Fellowship", fellow);
                        writer.WriteRaw("\n");*/
                        //fellowship end
                        //HospAffl
                        /*string hospaff = drow["HospitalName"].ToString();//bad naming of fields..
                        writer.WriteElementString("HospAffl", hospaff);
                        writer.WriteRaw("\n");*/
                        //HospAffl  END


                        //writer.WriteEndElement();
                        //writer.WriteRaw("\n");
                    }
                   
                    writer.WriteEndDocument();
                    writer.Flush();
                    writer.Close();
                    //Response.End();
                    base.Response.End();
                }//end if no errors
            }
            catch (Exception x)
            {
                writer.WriteElementString("ERROR", "Catch exception:" + x.Message);
                writer.WriteEndDocument();
                writer.Flush();
                writer.Close();
                base.Response.End();
            }
        }
    }
}
